﻿if object_id('fn_MapHTML_InspVisitXmissionStructure') is not null
DROP FUNCTION fn_MapHTML_InspVisitXmissionStructure

GO

CREATE FUNCTION [dbo].[fn_MapHTML_InspVisitXmissionStructure]
(
	@InspectionVisitPoleId int
)
RETURNS varchar(max)
AS
BEGIN
	declare @img xml
	--set @img = '<img src="http://dev-poleposition.com/DEV/Icons/transparent_150.png" />'

	declare @html nvarchar(max)
	set @html = 
	(
		select
			'Inspection Visit XMission' as 'TITLE'
			,Pole.*
			,map.Name as 'MapName'
			,sectionFk as 'SectionName'
			,species.Name as 'SpecieName'
			,classes.Name as 'ClassName'
			,treatments.Name as 'TreatmentName'
			,topTypes.Name as 'TopTypeName'
			,Pole.LInspector as 'LastInspection' 
			,Number
			,Length
			,Year
			,results.Comments as 'Comments'
			,CASE 
				when results.bElectrical = 1 then 'Yes'
				else 'No'
			 END as 'Electrical'
			,results.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
			,results.Vendor_InspVisit_XmissionStructures_ResultTypeFk
			,resultTypes.Name as 'ResultTypeName'
			,inspectionType.Name as 'InspectionTypeName'
			,inspector.Name as 'InspectorName'
			,vendors.Name as 'VendorName'

			--Inspection result notes
			,(select 
					results.Vendor_InspVisit_XmissionStructures_ResultId,
					Notes.*
			  from 
					tb_Vendor_InspVisit_XmissionStructures_ResultsXNotes resultxNotes
			  inner join tb_Vendor_InspVisit_XmissionStructures_Notes Notes
					on resultxNotes.Vendor_InspVisit_XmissionStructures_NoteFk = notes.Vendor_InspVisit_XmissionStructures_NoteId
			  where 
					resultxNotes.Vendor_InspVisit_XmissionStructures_ResultFk = results.Vendor_InspVisit_XmissionStructures_ResultId
			for xml path ('NOTE'), type) as '*'

			-- visit images <IMAGE href="" src="" />
			,(select
				replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_InspVisit_XmissionStructures_ImageId), '{1}', 'InspectionPoleXmission'), '{2}', 85), '{3}', 85) as '@src'
				--,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.Vendor_InspVisit_XmissionStructures_ImageId), '{1}', 'InspectionPoleXmission'), '{2}', 480), '{3}', 640) as '@href'
				,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_InspVisit_XmissionStructures_ImageId), '{1}', 'InspectionPoleXmission'), '{2}', 640), '{3}', 480)) as '@href'
				,Images.*
			from 
				tb_Vendor_InspVisit_XmissionStructures_Images as Images
			where 
				Images.Vendor_InspVisit_XMissionStructure_PoleFk = @InspectionVisitPoleId
			for xml path ('IMAGE'), type) as '*'

		from 
			tb_Vendor_InspVisit_XmissionStructures_Poles as Pole
			LEFT JOIN tb_Settings as Href 
				ON Href.SettingId = 25
			LEFT JOIN tb_Settings as Src 
				ON Src.SettingId = 24
			LEFT JOIN tb_Utility_Polygons map
				ON Pole.MapFk = map.Utility_PolygonId
			LEFT JOIN tb_XMissionStructures_PoleSpecies species
				ON Pole.SpecieFk = species.XMissionStructures_PoleSpeciesId 
			LEFT JOIN tb_XMissionStructures_PoleClasses classes
				ON Pole.ClassFk = classes.XMissionStructures_PoleClassId 
			LEFT JOIN tb_XMissionStructures_PoleTreatments treatments
				ON Pole.TreatmentFk = treatments.XMissionStructures_PoleTreatmentId 				
			LEFT JOIN tb_XMissionStructures_PoleTopTypes topTypes
				ON Pole.TopTypeFk = topTypes.XMissionStructures_PoleTopTypeId
			LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Results results
				ON Pole.Vendor_InspVisit_XMissionStructure_PoleId = results.Vendor_InspVisit_XMissionStructure_PoleFk
			LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes resultTypes
				ON resultTypes.Vendor_InspVisit_XmissionStructures_ResultTypeId = results.Vendor_InspVisit_XmissionStructures_ResultTypeFk
			LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type inspectionType
				ON inspectionType.Vendor_InspVisit_XmissionStructures_Inspection_TypeId = results.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
			LEFT JOIN tb_Vendor_Contacts inspector
				ON inspector.Vendor_ContactId = Pole.Vendor_ContactFK
			LEFT JOIN tb_Utilities utility
				ON utility.UtilityId = Pole.CompanyFk
			LEFT JOIN tb_Vendor_Projects vendorProjects
				ON vendorProjects.Vendor_ProjectId = Pole.Vendor_ProjectFk
			LEFT JOIN tb_Vendors vendors
				ON vendors.VendorId = vendorProjects.VendorFk
		where
			Pole.Vendor_InspVisit_XMissionStructure_PoleId = @InspectionVisitPoleId
		for xml path ('VISIT')
)	
	return @html
END
